package com.example.utils.sqlutils;

import java.util.List;

public class SqlBuilder {

    private static final String COUNT="SELECT COUNT(1) ";

    private final StringBuffer selectBuffer;

    private final StringBuffer stringBuffer;

    private final StringBuffer pageBuffer;

    private final StringBuffer orderByBuffer;


    /**
     * 添加查询sql
     * @param
     * @return
     */
    public SqlBuilder select(String select){
        String sql=selectBuffer.toString().toUpperCase();
        if(!sql.contains("SELECT")){
            selectBuffer.append("SELECT ");
        }
        selectBuffer.append(select);
        return this;
    }


    /**
     *
     * @param field
     * @param obj
     * @param trueObj
     * @param falseObj
     * @return
     */
    public SqlBuilder fieldCaseWhen(String field,Object obj,Object trueObj,Object falseObj){
        selectBuffer.append(", CASE WHEN (").append(field).append("=").append(obj)
                .append(") THEN ").append(trueObj).append(" ELSE ");
        if(falseObj!=null){
            selectBuffer.append(falseObj);
        }else {
            stringBuffer.append(field);
        }
        stringBuffer.append(" END");
        return this;
    }


    public SqlBuilder selectList(List<String> stringList){
        String select=String.join(",",stringList);
        this.select(select);
        return this;
    }


    /**
     *
     * @param tableName 表名
     * @return SqlBuilder对象
     */
    public SqlBuilder from(String tableName){
        stringBuffer.append(" from ").append(tableName);
        return this;
    }

    /**
     * and 连接
     * @param field and连接查询的字段
     * @return
     */
    public SqlBuilder and(String field){
        appendCondition(stringBuffer,"and",field);
        return this;
    }

    /**
     * or 连接
     * @param field
     * @return
     */
    public SqlBuilder or(String field){
        appendCondition(stringBuffer,"or",field);
        return this;
    }

    public SqlBuilder eq(Object obj){
        stringBuffer.append(" = ").append("'").append(obj).append("'");
        return this;
    }

    /**
     * like 查询
     * @param obj (查询的对象)
     * @return
     */
    public SqlBuilder like(Object obj){
        stringBuffer.append(" LIKE ").append("'%").append(obj).append("%'");
        return this;
    }

    /**
     * 右模糊
     * @param obj
     * @return
     */
    public SqlBuilder rightLike(Object obj){
        stringBuffer.append(" LIKE ").append("'").append(obj).append("%'");
        return this;
    }

    public SqlBuilder isNotNull(){
        stringBuffer.append(" IS NOT NULL");
        return this;
    }

    /**
     * 获取查询条数sql
     * @return
     */
    public String getCountSql(){
        return COUNT+stringBuffer.toString();
    }


    public SqlBuilder orderByDesc(Object obj){
        orderByBuffer.append(" ORDER BY ").append(obj).append(" DESC ");
        return this;
    }

    /**
     * 设置分页sql
     * @param page
     * @param size
     */
    public void page(Integer page,Integer size){
        page=(page-1)*size;
        pageBuffer.append(selectBuffer.toString()).append(stringBuffer.toString());
        if(orderByBuffer.length()>0){
            pageBuffer.append(orderByBuffer.toString());
        }
        pageBuffer.append(" LIMIT ").append(page).append(",").append(size);
    }

    /**
     * 获取分页sql
     * @return
     */
    public String getPage(){
        return pageBuffer.toString();
    }


    @Override
    public String toString(){
        return selectBuffer.append(stringBuffer.toString()).toString();
    }

    public StringBuffer getstringBuffer(){
        return this.selectBuffer;
    }




    public SqlBuilder() {
        stringBuffer=new StringBuffer();
        pageBuffer=new StringBuffer();
        selectBuffer=new StringBuffer();
        orderByBuffer=new StringBuffer();
    }



    private void appendCondition(StringBuffer stringBuffer,String appendCondition,String field){
        String sql=stringBuffer.toString().toUpperCase();
        if(!sql.contains("WHERE")){
            appendCondition="WHERE";
        }
        stringBuffer.append(" ").append(appendCondition)
                .append(" ").append(field);
    }
}
